跳到主要内容

MySQL 索引应该怎么选择？

总之先牢记！！ 索引即数据，数据即索引

创建索引的原则​

索引虽好，但也不是无限制的使用，最好符合一下几个原则

1、最左前缀匹配原则，组合索引非常重要的原则，mysql 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如

a = 1 and b = 2 and c > 3 and d = 4

如果建立 (a,b,c,d) 顺序的索引，d 是用不到索引的，如果建立 (a,b,d,c) 的索引则都可以用到，a,b,d 的顺序可以任意调整。

2、较频繁作为查询条件的字段才去创建索引

3、更新频繁字段不适合创建索引

4、若是不能有效区分数据的列不适合做索引列（如性别，男女未知，最多也就三种，区分度实在太低，可以通过 count() 函数查看字段的差异值，越大越好）

5、尽量的扩展索引，不要新建索引。比如表中已经有 a 的索引，现在要加 (a,b) 的索引，那么只需要修改原来的索引即可。

提示

通过扩展现有索引，可以充分利用已有的索引结构，避免重复存储相同的索引数据，从而减少存储空间的占用。此外，由于索引的创建和维护都需要一定的时间和资源，通过修改现有索引可以减少额外的索引维护开销。

然而，需要注意的是，并非所有的索引扩展都适合修改现有索引来完成。有些情况下，可能需要根据具体的查询需求创建新的索引才能更好地支持查询优化。在做出决策时，需要综合考虑索引的使用情况、查询性能以及数据更新的频率等因素。

6、定义有外键的数据列一定要建立索引。

7、对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8、对于定义为 text、image 和 bit 的数据类型的列不要建立索引。

创建索引时需要注意什么？​

非空字段：应该指定列为 NOT NULL，除非你想存储 NULL。在 mysql 中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。应该用 0、一个特殊的值或者一个空串代替空值；

取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过 count() 函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；

索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次 IO 操作获取的数据越大效率越高。

使用索引查询一定能提高性能吗？​

使用索引查询可以提高查询性能的情况是很常见的，但并不是绝对的。索引的性能提升取决于多个因素，包括数据库的设计、查询的复杂性、数据分布和索引的使用方式等。有一些情况下索引的使用可能不会提高性能或甚至导致性能下降：

  1. 低选择性的查询：如果查询条件不具备足够的选择性，即需要返回大部分数据行，使用索引可能会导致不必要的索引扫描和额外的 IO 操作，降低查询性能。

  2. 数据更新频繁：当表中的数据频繁更新时，维护索引的开销可能超过了查询性能的提升，导致性能下降。

  3. 不合适的索引设计：索引的设计需要根据具体的查询需求和数据访问模式进行优化，如果索引设计不合理，可能会导致索引冗余、过多或者不必要的索引，从而降低查询性能。

索引带来的代价​

通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

  1. 存储空间：索引需要占用额外的存储空间。索引数据结构存储索引键值及其对应的指针或数据位置信息。对于大型表或多个索引的情况，索引可能占用相当大的存储空间。

  2. 写入性能：当对表进行插入、更新或删除操作时，索引也需要进行相应的维护。每次数据的改变都会导致索引的更新，这会增加写入操作的开销。这意味着每条记录的 INSERT，DELETE，UPDATE 将为此多付出 4，5 次的磁盘 I/O。

  3. 索引维护成本：随着数据的变化，索引需要进行维护以保持数据的一致性和可用性。维护索引需要消耗计算资源和时间，特别是在大型表或频繁更新的环境中，索引维护成本可能变得显著。

  4. 查询优化成本：数据库查询优化器需要考虑索引的选择、使用和组合，以生成最优的查询执行计划。这需要消耗一定的计算资源和时间。

  5. 索引更新对性能的影响：索引的存在可能导致查询计划的变化。在某些情况下，索引可能不适用于某些查询，并且可能导致性能下降。

因此，在设计和使用索引时，需要权衡索引带来的性能提升和代价。合理选择和设计索引，避免过多或不必要的索引，以及定期优化和维护索引，可以最大程度地减少索引带来的代价。

通过 Cardinality 值去判断是否需要索引 ⭐​

并不是在所有的查询条件中出现的列都需要添加索引。对于什么时候添加 B+ 树索引，一般的经验是，在访问表中可取值范围大时使用 B+ 树索引才有意义。对于性别字段，地区字段、类型字段，它们可取值的范围很小，称为低选择性。这种高低选择性，在数据库中称为基数（Cardinality）。

下面举个实际的例子

SELECT FROM student WHERE sex = 'M';

按性别进行查询时，可取值的范围一般只有 'M'、'F'。 因此上述 SQL 语句得到的结果可能是该表 50% 的数据（假设男女比例1 : 1），这时添加 B+ 树索引是完全没有必要的。

相反，如果某个字段的取值范围很广，几乎没有重复，即属于高选择性，则此时使用 B+ 树索引是最适合的。例如，对于姓名字段，基本上在一个应用中不允许重名的出现。

提示

Cardinality（基数）是数据库术语，用于描述某个数据集合中不同值的数量或唯一值的数量。在关系数据库中，基数通常用于衡量表中某个列的不同取值的数量。

基数可以分为三种类型：

  1. 低基数（Low Cardinality）：表示某列的取值非常有限（通常低于5%），只有很少的不同取值。例如，一个布尔类型的列只有两个可能的取值：True 或 False。低基数列的取值相对较少，可能不是很有信息量。

  2. 中等基数（Medium Cardinality）：表示某列的取值数量适中（通常在5%到20%之间），有一定的变化和差异性。这种类型的列可能包含多个不同的取值，但不至于非常多。

  3. 高基数（High Cardinality）：表示某列的取值非常多（通常高于20%）时，几乎每个行都有不同的取值。例如，一个包含用户 ID 的列，每个用户可能都有唯一的 ID。高基数列的取值非常丰富，可能提供了很大的信息量。

基数对数据库索引的选择和性能有重要影响。在索引设计中，选择适当的列作为索引可以提高查询性能。对于高基数列，由于取值的丰富性，索引可以更好地帮助定位和过滤数据，提供更高的查询效率。相反，对于低基数列，索引可能不会带来明显的性能提升，因为索引的选择性较低。

因此，在设计数据库架构和索引时，需要考虑列的基数，以便选择合适的索引策略和优化查询性能。

上面讲述了这么多 Cardinality 的概念，我们可知只要满足 高基数（High Cardinality） 就算一个好的索引？怎样查看索引是否是高选择性的呢？

主要有以下方式：

1、使用 SHOW INDEX 命令查看索引信息，包括基数：

-- SHOW INDEX FROM table_name;
SHOW INDEX FROM `device_happy_order`;

在实际应用中，Cardinality / n_rows_in_table 应尽可能地接近 1。

2、通过执行 COUNT(DISTINCT 列名) 查询，您可以获取某个列的不同值的数量。这将给出该列的基数。

select COUNT(DISTINCT trade_no) FROM `device_happy_order`;

同上，这个百分比越大越好

Reference​